/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package edu.udlap.spring2008.is300.finalexam.app.mvc.model.database;

import java.sql.*;
import java.util.*;
import java.io.File;
/**
 *
 * @author LILIANA ROJAS
 */
public class DerbyEmbeddedDB {
    
    private DatabaseFacade db;
    private Driver driver;
    private String connectionString;
    private String usernameString;
    private String passwordString;
    private Properties properties;
    private boolean exists = false;
    private String dbName = "images";  
    private Connection conn;
    private static final String strCreateCategoryTable =
        "create table IMG.CATEGORY(" +
            "ID_CATEGORY INTEGER NOT NULL," +
            "CATEGORY VARCHAR(20)," +
            "PRIMARY KEY(ID_CATEGORY))";
        private static final String strCreateImageTable = 
        "create table IMG.IMAGE (" +
            "ID_IMAGE INTEGER NOT NULL," +
            "ADDRESS VARCHAR(300)," +
            "DESCRIPTION VARCHAR(500),"+
            "ID_CATEGORY INTEGER CONSTRAINT CATEGORY_FK REFERENCES IMG.CATEGORY," +
            "ID_FAVORITE INTEGER CONSTRAINT FAVORITE_FK REFERENCES IMG.FAVORITE," +
            "ID_PRIVACY INTEGER CONSTRAINT PRIVACY_FK REFERENCES IMG.PRIVACY," +
            "ID_SIZE INTEGER CONSTRAINT SIZE_FK REFERENCES IMG.SIZE,"+
            "ID_FORMAT INTEGER CONSTRAINT FORMAT_FK REFERENCES IMG.FORMAT," +
            "PRIMARY KEY(ID_IMAGE))";
    private static final String strCreateFavoriteTable =
        "create table IMG.FAVORITE(" +
            "ID_FAVORITE INTEGER NOT NULL,"+
            "FAVORITE SMALLINT," +
            "PRIMARY KEY(ID_FAVORITE))";
    private static final String strCreatePrivacyTable =
        "create table IMG.PRIVACY(" +
            "ID_PRIVACY INTEGER NOT NULL," +
            "PRIVACY VARCHAR(15)," +
            "PRIMARY KEY(ID_PRIVACY))";
    private static final String strCreateDescriptionTable =
        "create table IMG.DESCRIPTION(" +
            "ID_DESCRIPTION INTEGER NOT NULL," +
            "DESCRIPTION VARCHAR(500)," +
            "PRIMARY KEY(ID_DESCRIPTION))";
    private static final String strCreateSizeTable =
        "create table IMG.SIZE(" +
            "ID_SIZE INTEGER NOT NULL," +
            "SIZE VARCHAR(10)," +
            "PRIMARY KEY(ID_SIZE))";
    private static final String strCreateFormatTable =
        "create table IMG.FORMAT(" +
            "ID_FORMAT INTEGER NOT NULL," +
            "FORMAT VARCHAR(10)," +
            "PRIMARY KEY(ID_FORMAT))";
   
    public DerbyEmbeddedDB()
    {
        setDBSystemDir();
        properties = loadProperties();
        
        connectionString = getDatabaseUrl();
        usernameString = properties.getProperty("user");
        passwordString = properties.getProperty("password");
        driver =  new org.apache.derby.jdbc.EmbeddedDriver();
        db = new DatabaseFacade(driver,connectionString, usernameString, passwordString);
        if(db.registerDriver());
            System.out.println("registrado");
        if(!exists)
            exists = createDatabase();
        conn = db.getConnection();
        
    }

    private boolean createDatabase() {
        boolean bCreated = false;
        properties.put("create", "true");
        
        if(db.connect())
            System.out.println("conexion ok");    
        bCreated = createTables(db.getConnection());
        properties.remove("create");
        return bCreated;        
    }

    private boolean createTables(Connection connection) {
        boolean bCreatedTables = false;
        Statement statement = null;
        try{
            statement = connection.createStatement();
            statement.executeUpdate("DROP TABLE IMG.IMAGE");
            statement.executeUpdate("DROP TABLE IMG.FORMAT");
            statement.executeUpdate("DROP TABLE IMG.SIZE");
            statement.executeUpdate("DROP TABLE IMG.DESCRIPTION");
            statement.executeUpdate("DROP TABLE IMG.PRIVACY");
            statement.executeUpdate("DROP TABLE IMG.FAVORITE");
            statement.executeUpdate("DROP TABLE IMG.CATEGORY");
        }
        catch(SQLException e)
        {
            System.out.println("Tablas no definidas aun.");
        }
        try {
            //statement = connection.createStatement();
            statement.execute(strCreateCategoryTable);
            statement.execute(strCreateFavoriteTable);
            statement.execute(strCreatePrivacyTable);
            statement.execute(strCreateDescriptionTable);
            statement.execute(strCreateSizeTable);
            statement.execute(strCreateFormatTable);
            statement.execute(strCreateImageTable);
            statement.executeUpdate("INSERT INTO IMG.CATEGORY VALUES (1 , 'animals')");
            statement.executeUpdate("INSERT INTO IMG.CATEGORY VALUES (2 , 'sports')");
            statement.executeUpdate("INSERT INTO IMG.CATEGORY VALUES (3 , 'famous people')");
            statement.executeUpdate("INSERT INTO IMG.CATEGORY VALUES (4 , 'models')");
            statement.executeUpdate("INSERT INTO IMG.CATEGORY VALUES (5 , 'cars')");
            statement.executeUpdate("INSERT INTO IMG.CATEGORY VALUES (6 , 'science')");
            statement.executeUpdate("INSERT INTO IMG.CATEGORY VALUES (7 , 'technology')");
            statement.executeUpdate("INSERT INTO IMG.CATEGORY VALUES (8 , 'computers')");
            statement.executeUpdate("INSERT INTO IMG.CATEGORY VALUES (9 , 'fun')");
            statement.executeUpdate("INSERT INTO IMG.CATEGORY VALUES (10 , 'comics')");
            statement.executeUpdate("INSERT INTO IMG.SIZE VALUES (1 , 'very small')");
            statement.executeUpdate("INSERT INTO IMG.SIZE VALUES (2 , 'small')");
            statement.executeUpdate("INSERT INTO IMG.SIZE VALUES (3 , 'medium')");
            statement.executeUpdate("INSERT INTO IMG.SIZE VALUES (4 , 'wallpaper')");
            statement.executeUpdate("INSERT INTO IMG.PRIVACY VALUES (1 , 'public')");
            statement.executeUpdate("INSERT INTO IMG.PRIVACY VALUES (2 , 'private')");
            statement.executeUpdate("INSERT INTO IMG.PRIVACY VALUES (3 , 'my eyes only')");
            statement.executeUpdate("INSERT INTO IMG.FORMAT VALUES (1 , 'jpg')");
            statement.executeUpdate("INSERT INTO IMG.FORMAT VALUES (2 , 'gif')");
            statement.executeUpdate("INSERT INTO IMG.FORMAT VALUES (3 , 'png')");
            statement.executeUpdate("INSERT INTO IMG.FAVORITE VALUES (1 , 0)");
            statement.executeUpdate("INSERT INTO IMG.FAVORITE VALUES (2 , 1)");
            statement.executeUpdate("INSERT INTO IMG.IMAGE VALUES (1 , 'C:\\my pictures\\image1.jpg','my pet',1,1,1,3,1)");
            statement.executeUpdate("INSERT INTO IMG.IMAGE VALUES (2 , 'C:\\my pictures\\image2.gif','my pet',1,1,1,2,2)");
            statement.executeUpdate("INSERT INTO IMG.IMAGE VALUES (3 , 'C:\\my pictures\\image3.jpg','my pet',1,2,1,3,1)");
            bCreatedTables = true;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        
        return bCreatedTables;
    }

    private String getDatabaseUrl() {
        String dbUrl = properties.getProperty("derby.url") + dbName;
        return dbUrl;      
    }

    /** Sets the derby.system.home for the computer where the 
     * application runs.
     * */
    private void setDBSystemDir() 
    {
        // decide on the db system directory
        String userHomeDir = System.getProperty("user.home", ".");
        System.out.println(userHomeDir);
        String systemDir = userHomeDir + "\\.images";
        System.out.println(systemDir);
        System.setProperty("derby.system.home", systemDir);
        
        // create the db system directory
        File fileSystemDir = new File(systemDir);
        fileSystemDir.mkdir();
    }    
     /**
      * 
      * @return
      */
    private Properties loadProperties()
    {
        properties = new Properties();
        
        properties.put("user","user");
        properties.put("password","user");
        properties.put("derby.driver", "org.apache.derby.jdbc.EmbeddedDriver");
        properties.put("derby.url","jdbc:derby:");
        properties.put("db.table","IMAGE");
        properties.put("db.schema","IMG");
        return properties;
    }
    public String doQueryCategory(String str)
    {        
        String statement = "SELECT * FROM IMG.IMAGE,IMG.FAVORITE WHERE IMAGE.ID_FAVORITE = FAVORITE.ID_FAVORITE";//"select * from IMG.CATEGORY";
        Connection conn = db.getConnection();
       
        PreparedStatement pstm;
        
        String res = "";
        try
        {
           //db.connect();
           conn = DriverManager.getConnection(getDatabaseUrl(), properties);
           pstm = conn.prepareStatement(statement);
           ResultSet r = pstm.executeQuery();
           while(r.next())
               res += r.getInt("ID_IMAGE")+ "\t" +r.getString("ADDRESS");
           
           r.close();
           pstm.close();
           conn.close();
           //db.closeConnection();
            
        }
        catch(SQLException e)
        {
            System.out.println("****Vacio");
            e.printStackTrace();
        }
        return res;
    }
    
    public void connect()
    {
        //db.connectProperties(properties);
    }
    public void disconnect()
    {
        db.closeConnection();
    }
    
   public static void main(String str[])
    {
        DerbyEmbeddedDB db = new DerbyEmbeddedDB();
        //System.out.println(db.doQueryCategory("Favorite"));
        
    }
}
